package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bizlogic.bookinfo.domain.BookInfo;
import utils.JDBCUtil;


public class BookInfoDao {

    /*
     * add检查是否是重复书
     */
	public boolean CheckExitsAdd(String bookname, String author) {
		Connection connection = JDBCUtil.getConnection();
		String sql = "select *from bookinfo where bookname=? and author=?";
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;
		try {
			pStatement = connection.prepareStatement(sql);
			pStatement.setString(1, bookname);
			pStatement.setString(2, author);
			resultSet = pStatement.executeQuery();
			if (resultSet.next()) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(resultSet, pStatement, connection);
		}
		return false;

	}

	
	/*
     * Edit检查是否是重复书
     */
	public boolean CheckExitsEdit(String oldbookname, String newbookname, String author) {
		Connection connection = JDBCUtil.getConnection();
		String sql = "select *from bookinfo where bookname!=? and author=? and bookname=?";// ����Լ����������ͼ����Ϣ
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;
		try {
			pStatement = connection.prepareStatement(sql);
			pStatement.setString(1, oldbookname);
			pStatement.setString(2, author);
			pStatement.setString(3, newbookname);
			resultSet = pStatement.executeQuery();
			if (resultSet.next()) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(resultSet, pStatement, connection);
		}
		return false;

	}

	/*
     * 添加图书信息（返回新增成功的数据条数）
     */
	public int addBookInfo(BookInfo bookInfo) {

		Connection con = JDBCUtil.getConnection();
		String sql = "insert into bookinfo values (?,?,?,?,?,?)";
		PreparedStatement ps = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, bookInfo.getBookid());
			ps.setString(2, bookInfo.getBookname());
			ps.setString(3, bookInfo.getPublisher());
			ps.setString(4, bookInfo.getAuthor());
			ps.setInt(5, bookInfo.getBooktype());
			ps.setInt(6, bookInfo.getRemain());
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(null, ps, con);
		}
		return i;

	}

	/*
     * 更新图书信息（返回更新成功的数据条数）
     */
	public int updateBookInfo(BookInfo bookinfo) {
		Connection connection = JDBCUtil.getConnection();
		String sql = "update bookinfo  set booktype =?,publisher =?,bookname =?,author =?,remain =? where bookid =?";
		PreparedStatement pStatement = null;
		int i = 0;
		try {
			pStatement = connection.prepareStatement(sql);
			pStatement.setInt(1, bookinfo.getBooktype());
			pStatement.setString(2, bookinfo.getPublisher());
			pStatement.setString(3, bookinfo.getBookname());
			pStatement.setString(4, bookinfo.getAuthor());
			pStatement.setInt(5, bookinfo.getRemain());
			pStatement.setString(6, bookinfo.getBookid());
			i = pStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(null, pStatement, connection);
		}
		return i;

	}

	/*
     * 根据图书id删除图书信息（传入单个图书id，返回删除成功的数据条数）
     */
	public int deleteBookinfoByBookId(String bookids) {
		Connection connection = JDBCUtil.getConnection();
		String sql = "delete from bookinfo where find_in_set(bookid,?)";
		String sql2 = "delete from borrowinfo where find_in_set(bookid,?)";
		PreparedStatement pStatement = null;
		int i = 0;
		int j = 0;
		int result = 0;
		try {
			connection.setAutoCommit(false);
			pStatement = connection.prepareStatement(sql);
			pStatement.setString(1, bookids);
			i = pStatement.executeUpdate();
			if (i > 0) {
				pStatement.close();
				pStatement = connection.prepareStatement(sql2);
				pStatement.setString(1, bookids);
				j = pStatement.executeUpdate();
			}
			if (i > 0 && j >= 0) {
				result = 1;
				connection.commit();
			} else {
				result = 0;
				connection.rollback();
			}

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			JDBCUtil.closeConnection(null, pStatement, connection);
		}
		return result;
	}

	/*
     * 根据图书id查询图书信息 （返回查询的实体）
     */
	public BookInfo queryBookInfoByID(String bookId) {
		Connection connection = JDBCUtil.getConnection();
		String sql = "select * from  bookinfo  where bookid =?";
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;
		try {
			pStatement = connection.prepareStatement(sql);
			pStatement.setString(1, bookId);
			resultSet = pStatement.executeQuery();
			while (resultSet.next()) {
				BookInfo bookInfo = new BookInfo();
				bookInfo.setAuthor(resultSet.getString("author"));
				bookInfo.setBookid(resultSet.getString("bookid"));
				bookInfo.setBookname(resultSet.getString("bookname"));
				bookInfo.setBooktype(resultSet.getInt("booktype"));
				bookInfo.setPublisher(resultSet.getString("publisher"));
				bookInfo.setRemain(resultSet.getInt("remain"));
				return bookInfo;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(resultSet, pStatement, connection);
		}
		return null;

	}

	/*
     * 根据关键词对图书信息列表进行分页查询（假设关键词为String bookname、Integer booktype）
     */
	public List<BookInfo> queryBookinfoByKeyWords(int pageIndex, int pageSize, String bookname, Integer booktype) {
		Connection con = JDBCUtil.getConnection();
		int indexnum = pageIndex * pageSize;
		PreparedStatement ps = null;
		ResultSet rs = null;

		List<BookInfo> bookInfoList = new ArrayList<BookInfo>();
		BookInfo bookInfo = null;

		StringBuffer sb = new StringBuffer();
		sb.append(" select * from bookinfo where 1=1 ");

		if ((!"".equals(bookname)) && bookname != null) {
			sb.append(" and bookname like concat('%', ? ,'%') ");
		}
		if (booktype != null) {
			sb.append(" and booktype = ? ");
		}
		sb.append(" limit ?,?");

		String sql = sb.toString();
		int i = 1;
		try {
			ps = con.prepareStatement(sql);
			if ((!"".equals(bookname)) && bookname != null) {
				ps.setString(i++, bookname);
			}
			if (booktype != null) {
				ps.setInt(i++, booktype);
			}
			ps.setInt(i++, indexnum);
			ps.setInt(i++, pageSize);
			rs = ps.executeQuery();
			while (rs.next()) {
				bookInfo = new BookInfo();
				bookInfo.setBookid(rs.getString("bookid"));
				bookInfo.setBookname(rs.getString("bookname"));
				bookInfo.setAuthor(rs.getString("author"));
				bookInfo.setPublisher(rs.getString("publisher"));
				bookInfo.setBooktype(rs.getInt("booktype"));
				bookInfo.setRemain(rs.getInt("remain"));
				bookInfoList.add(bookInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(rs, ps, con);
		}
		return bookInfoList;

	}

	
	/*
     * 查询图书信息表中所有数据的条数
     */
	public int findLength() {
		Connection connection = JDBCUtil.getConnection();
		String sql = "select *from bookinfo";
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;
		List<BookInfo> lBookInfos = new ArrayList<>();
		try {
			pStatement = connection.prepareStatement(sql);
			resultSet = pStatement.executeQuery();
			while (resultSet.next()) {
				BookInfo bookInfo = new BookInfo();
				bookInfo.setAuthor(resultSet.getString("author"));
				bookInfo.setBookid(resultSet.getString("bookid"));
				bookInfo.setBookname(resultSet.getString("bookname"));
				bookInfo.setBooktype(resultSet.getInt("booktype"));
				bookInfo.setPublisher(resultSet.getString("publisher"));
				bookInfo.setRemain(resultSet.getInt("remain"));
				lBookInfos.add(bookInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(resultSet, pStatement, connection);
		}
		return lBookInfos.size();

	}

}
